-- =============================================
-- 教材管理系统常用查询示例
-- =============================================

USE TextbookManagement;
GO

-- =============================================
-- 1. 基础查询示例
-- =============================================

-- 1.1 查询所有教材信息
SELECT * FROM V_TextbookDetail;

-- 1.2 按分类查询教材
SELECT 
    t.title,
    t.author,
    t.publisher,
    t.price,
    c.category_name
FROM Textbook t
INNER JOIN Category c ON t.category_id = c.category_id
WHERE c.category_name = '数据库';

-- 1.3 查询库存不足的教材
SELECT * FROM V_InventoryAlert 
WHERE stock_status = '库存不足';

-- 1.4 查询指定价格范围的教材
SELECT title, author, price 
FROM Textbook 
WHERE price BETWEEN 40 AND 50
ORDER BY price;

-- =============================================
-- 2. 统计查询示例
-- =============================================

-- 2.1 按分类统计教材数量
SELECT 
    c.category_name,
    COUNT(t.textbook_id) AS textbook_count,
    AVG(t.price) AS avg_price
FROM Category c
LEFT JOIN Textbook t ON c.category_id = t.category_id
GROUP BY c.category_id, c.category_name
ORDER BY textbook_count DESC;

-- 2.2 统计各供应商的订单情况
SELECT 
    s.supplier_name,
    COUNT(po.order_id) AS order_count,
    SUM(po.total_amount) AS total_amount,
    AVG(po.total_amount) AS avg_amount
FROM Supplier s
LEFT JOIN Purchase_Order po ON s.supplier_id = po.supplier_id
GROUP BY s.supplier_id, s.supplier_name
ORDER BY total_amount DESC;

-- 2.3 统计各课程的教材需求
SELECT 
    c.course_name,
    c.department,
    COUNT(ct.textbook_id) AS textbook_count,
    SUM(ct.required_quantity) AS total_required
FROM Course c
LEFT JOIN Course_Textbook ct ON c.course_id = ct.course_id
GROUP BY c.course_id, c.course_name, c.department
ORDER BY total_required DESC;

-- =============================================
-- 3. 复杂查询示例
-- =============================================

-- 3.1 查询需要补货的教材（库存低于需求量）
SELECT 
    t.title,
    t.isbn,
    i.current_stock,
    SUM(ct.required_quantity) AS total_required,
    SUM(ct.required_quantity) - i.current_stock AS need_purchase
FROM Textbook t
INNER JOIN Inventory i ON t.textbook_id = i.textbook_id
INNER JOIN Course_Textbook ct ON t.textbook_id = ct.textbook_id
WHERE ct.semester = '2024春'
GROUP BY t.textbook_id, t.title, t.isbn, i.current_stock
HAVING SUM(ct.required_quantity) > i.current_stock
ORDER BY need_purchase DESC;

-- 3.2 查询最近30天的出入库记录
SELECT 
    t.title,
    sr.record_type,
    CASE sr.record_type 
        WHEN 1 THEN '入库' 
        WHEN 2 THEN '出库' 
    END AS record_type_name,
    sr.quantity,
    sr.record_date,
    sr.operator,
    sr.remark
FROM Stock_Record sr
INNER JOIN Textbook t ON sr.textbook_id = t.textbook_id
WHERE sr.record_date >= DATEADD(DAY, -30, GETDATE())
ORDER BY sr.record_date DESC;

-- 3.3 查询各院系的教材采购预算
SELECT 
    c.department,
    COUNT(DISTINCT ct.textbook_id) AS textbook_types,
    SUM(ct.required_quantity * t.price) AS total_budget
FROM Course c
INNER JOIN Course_Textbook ct ON c.course_id = ct.course_id
INNER JOIN Textbook t ON ct.textbook_id = t.textbook_id
WHERE ct.semester = '2024春'
GROUP BY c.department
ORDER BY total_budget DESC;

-- =============================================
-- 4. 数据维护查询
-- =============================================

-- 4.1 检查数据完整性
-- 检查是否有教材没有库存记录
SELECT t.textbook_id, t.title
FROM Textbook t
LEFT JOIN Inventory i ON t.textbook_id = i.textbook_id
WHERE i.textbook_id IS NULL;

-- 检查是否有孤立的分类（没有教材的分类）
SELECT c.category_id, c.category_name
FROM Category c
LEFT JOIN Textbook t ON c.category_id = t.category_id
WHERE t.textbook_id IS NULL AND c.parent_id IS NOT NULL;

-- 4.2 查找重复的ISBN
SELECT isbn, COUNT(*) as count
FROM Textbook
GROUP BY isbn
HAVING COUNT(*) > 1;

-- =============================================
-- 5. 业务报表查询
-- =============================================

-- 5.1 月度采购报表
SELECT 
    YEAR(po.order_date) AS year,
    MONTH(po.order_date) AS month,
    COUNT(po.order_id) AS order_count,
    SUM(po.total_amount) AS total_amount,
    AVG(po.total_amount) AS avg_amount
FROM Purchase_Order po
WHERE po.status IN (2, 3) -- 已确认或已完成
GROUP BY YEAR(po.order_date), MONTH(po.order_date)
ORDER BY year DESC, month DESC;

-- 5.2 教材销售排行榜（按出库量）
SELECT 
    t.title,
    t.author,
    t.publisher,
    SUM(sr.quantity) AS total_out_quantity
FROM Textbook t
INNER JOIN Stock_Record sr ON t.textbook_id = sr.textbook_id
WHERE sr.record_type = 2 -- 出库
    AND sr.record_date >= DATEADD(MONTH, -6, GETDATE()) -- 最近6个月
GROUP BY t.textbook_id, t.title, t.author, t.publisher
ORDER BY total_out_quantity DESC;

-- 5.3 供应商评价报表
SELECT 
    s.supplier_name,
    COUNT(po.order_id) AS order_count,
    SUM(po.total_amount) AS total_amount,
    AVG(DATEDIFF(DAY, po.order_date, po.create_time)) AS avg_delivery_days,
    COUNT(CASE WHEN po.status = 4 THEN 1 END) AS cancelled_orders
FROM Supplier s
LEFT JOIN Purchase_Order po ON s.supplier_id = po.supplier_id
WHERE po.order_date >= DATEADD(YEAR, -1, GETDATE()) -- 最近一年
GROUP BY s.supplier_id, s.supplier_name
ORDER BY total_amount DESC;

-- =============================================
-- 6. 测试数据操作
-- =============================================

-- 6.1 测试添加新教材
EXEC SP_AddTextbook 
    @isbn = '9787302999999',
    @title = '测试教材',
    @author = '测试作者',
    @publisher = '测试出版社',
    @price = 35.00,
    @category_id = 4,
    @description = '这是一本测试教材';

-- 6.2 测试教材入库
EXEC SP_TextbookInStock 
    @textbook_id = 1,
    @quantity = 50,
    @operator = '张三',
    @remark = '补充库存';

-- 6.3 测试教材出库
EXEC SP_TextbookOutStock 
    @textbook_id = 1,
    @quantity = 20,
    @operator = '李四',
    @remark = '课程发放';

-- 6.4 测试创建采购订单
DECLARE @order_id INT;

-- 插入采购订单
INSERT INTO Purchase_Order (order_no, supplier_id, order_date, total_amount, status, remark)
VALUES ('PO202412001', 1, GETDATE(), 0, 1, '测试采购订单');

SET @order_id = SCOPE_IDENTITY();

-- 插入订单明细
INSERT INTO Purchase_Order_Detail (order_id, textbook_id, quantity, unit_price, subtotal)
VALUES 
(@order_id, 1, 10, 45.00, 450.00),
(@order_id, 2, 15, 52.00, 780.00);

-- 查看订单信息
SELECT 
    po.order_no,
    po.order_date,
    s.supplier_name,
    po.total_amount,
    po.status
FROM Purchase_Order po
INNER JOIN Supplier s ON po.supplier_id = s.supplier_id
WHERE po.order_id = @order_id;

-- 查看订单明细
SELECT 
    t.title,
    pod.quantity,
    pod.unit_price,
    pod.subtotal
FROM Purchase_Order_Detail pod
INNER JOIN Textbook t ON pod.textbook_id = t.textbook_id
WHERE pod.order_id = @order_id;

-- =============================================
-- 7. 性能优化查询
-- =============================================

-- 7.1 查看表的记录数
SELECT 
    OBJECT_NAME(object_id) AS table_name,
    rows AS record_count
FROM sys.partitions
WHERE index_id IN (0, 1)
    AND OBJECT_NAME(object_id) IN (
        'Category', 'Textbook', 'Supplier', 'Purchase_Order', 
        'Purchase_Order_Detail', 'Inventory', 'Stock_Record', 
        'Course', 'Course_Textbook'
    )
ORDER BY rows DESC;

-- 7.2 查看索引使用情况
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECT_NAME(s.object_id) IN (
    'Category', 'Textbook', 'Supplier', 'Purchase_Order', 
    'Purchase_Order_Detail', 'Inventory', 'Stock_Record', 
    'Course', 'Course_Textbook'
)
ORDER BY table_name, index_name;

-- =============================================
-- 8. 数据备份和恢复示例
-- =============================================

-- 8.1 备份数据库
-- BACKUP DATABASE TextbookManagement 
-- TO DISK = 'C:\Backup\TextbookManagement_Full.bak'
-- WITH FORMAT, INIT, NAME = 'TextbookManagement-Full Database Backup';

-- 8.2 备份日志
-- BACKUP LOG TextbookManagement 
-- TO DISK = 'C:\Backup\TextbookManagement_Log.trn'
-- WITH FORMAT, INIT, NAME = 'TextbookManagement-Transaction Log Backup';

-- =============================================
-- 9. 用户权限管理示例
-- =============================================

-- 9.1 创建角色
-- CREATE ROLE TextbookManager;
-- CREATE ROLE TextbookOperator;
-- CREATE ROLE TextbookReader;

-- 9.2 分配权限
-- GRANT SELECT, INSERT, UPDATE, DELETE ON Textbook TO TextbookManager;
-- GRANT SELECT, INSERT, UPDATE ON Stock_Record TO TextbookOperator;
-- GRANT SELECT ON V_TextbookDetail TO TextbookReader;

PRINT '查询示例脚本执行完成！';
GO 